<!DOCTYPE html>



  


<html class="theme-next gemini use-motion" lang="zh-Hans">
<head><meta name="generator" content="Hexo 3.8.0">
  <meta charset="UTF-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<meta name="viewport" content="width=device-width, initial-scale=1, 
maximum-scale=1">
<script src="//cdn.bootcss.com/pace/1.0.2/pace.min.js"></script>
<meta name="theme-color" content="#222">

<script>
    (function () {
        if ('') {
            if (prompt('请输入文章密码') !== '') {
                alert('密码错误！');
                if (history.length === 1) {
                    location.replace("http://xxxxxxx.xxx"); // 这里替换成你的首页
                } else {
                    history.back();
                }
            }
        }
    })();
</script>


  
  
    
    
  <script src="/lib/pace/pace.min.js?v=1.0.2"></script>
  <link href="/lib/pace/pace-theme-minimal.min.css?v=1.0.2" rel="stylesheet">







<meta http-equiv="Cache-Control" content="no-transform">
<meta http-equiv="Cache-Control" content="no-siteapp">
















  
  
  <link href="/lib/fancybox/source/jquery.fancybox.css?v=2.1.5" rel="stylesheet" type="text/css">







<link href="/lib/font-awesome/css/font-awesome.min.css?v=4.6.2" rel="stylesheet" type="text/css">

<link href="/css/main.css?v=5.1.4" rel="stylesheet" type="text/css">


  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png?v=5.1.4">


  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png?v=5.1.4">


  <link rel="mask-icon" href="/images/logo.svg?v=5.1.4" color="#222">





  <meta name="keywords" content="mysql,">










<meta name="description" content="高性能MySQL读书笔记（四）  数据类型 范式设计 大表的Alter操作 相关资料   数据类型整数与小数类型  整数类型 : 有如下几种：TINYINT(1个字节),SMALLINT(2个字节),MEDIUMINT(3个字节),INT(4个字节),BIGINT(6个字节)">
<meta name="keywords" content="mysql">
<meta property="og:type" content="article">
<meta property="og:title" content="高性能MySQL读书笔记（四）">
<meta property="og:url" content="http://sabistian.cn/2019/12/01/高性能MySQL读书笔记(四)/index.html">
<meta property="og:site_name" content="Sabistian    |      Dear">
<meta property="og:description" content="高性能MySQL读书笔记（四）  数据类型 范式设计 大表的Alter操作 相关资料   数据类型整数与小数类型  整数类型 : 有如下几种：TINYINT(1个字节),SMALLINT(2个字节),MEDIUMINT(3个字节),INT(4个字节),BIGINT(6个字节)">
<meta property="og:locale" content="zh-Hans">
<meta property="og:updated_time" content="2020-03-03T01:27:34.205Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="高性能MySQL读书笔记（四）">
<meta name="twitter:description" content="高性能MySQL读书笔记（四）  数据类型 范式设计 大表的Alter操作 相关资料   数据类型整数与小数类型  整数类型 : 有如下几种：TINYINT(1个字节),SMALLINT(2个字节),MEDIUMINT(3个字节),INT(4个字节),BIGINT(6个字节)">



<script type="text/javascript" id="hexo.configurations">
  var NexT = window.NexT || {};
  var CONFIG = {
    root: '/',
    scheme: 'Gemini',
    version: '5.1.4',
    sidebar: {"position":"left","display":"post","offset":12,"b2t":false,"scrollpercent":false,"onmobile":false},
    fancybox: true,
    tabs: true,
    motion: {"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"slideDownIn","post_body":"slideDownIn","coll_header":"slideLeftIn","sidebar":"slideUpIn"}},
    duoshuo: {
      userId: '0',
      author: '博主'
    },
    algolia: {
      applicationID: '',
      apiKey: '',
      indexName: '',
      hits: {"per_page":10},
      labels: {"input_placeholder":"Search for Posts","hits_empty":"We didn't find any results for the search: ${query}","hits_stats":"${hits} results found in ${time} ms"}
    }
  };
</script>



  <link rel="canonical" href="http://sabistian.cn/2019/12/01/高性能MySQL读书笔记(四)/">





  <title>高性能MySQL读书笔记（四） | Sabistian    |      Dear</title>
  








</head>

<body itemscope itemtype="http://schema.org/WebPage" lang="zh-Hans">

  
  
    
  

  <div class="container sidebar-position-left page-post-detail">
    <div class="headband"></div>

    <header id="header" class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-wrapper">
  <div class="site-meta ">
    

    <div class="custom-logo-site-title">
      <a href="/" class="brand" rel="start">
        <span class="logo-line-before"><i></i></span>
        <span class="site-title">Sabistian    |      Dear</span>
        <span class="logo-line-after"><i></i></span>
      </a>
    </div>
      
        <p class="site-subtitle"></p>
      
  </div>

  <div class="site-nav-toggle">
    <button>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
      <span class="btn-bar"></span>
    </button>
  </div>
</div>

<nav class="site-nav">
  

  
    <ul id="menu" class="menu">
      
        
        <li class="menu-item menu-item-home">
          <a href="/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-home"></i> <br>
            
            首页
          </a>
        </li>
      
        
        <li class="menu-item menu-item-about">
          <a href="/about/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-user"></i> <br>
            
            关于
          </a>
        </li>
      
        
        <li class="menu-item menu-item-tags">
          <a href="/tags/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-tags"></i> <br>
            
            标签
          </a>
        </li>
      
        
        <li class="menu-item menu-item-categories">
          <a href="/categories/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-th"></i> <br>
            
            分类
          </a>
        </li>
      
        
        <li class="menu-item menu-item-archives">
          <a href="/archives/" rel="section">
            
              <i class="menu-item-icon fa fa-fw fa-archive"></i> <br>
            
            归档
          </a>
        </li>
      

      
    </ul>
  

  
</nav>



 </div>
    </header>

    <main id="main" class="main">
      <div class="main-inner">
        <div class="content-wrap">
          <div id="content" class="content">
            

  <div id="posts" class="posts-expand">
    

  

  
  
  

  <article class="post post-type-normal" itemscope itemtype="http://schema.org/Article">
  
  
  
  <div class="post-block">
    <link itemprop="mainEntityOfPage" href="http://sabistian.cn/2019/12/01/高性能MySQL读书笔记(四)/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="name" content="邱灵雨">
      <meta itemprop="description" content>
      <meta itemprop="image" content="/images/headicon.png">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="Sabistian    |      Dear">
    </span>

    
      <header class="post-header">

        
        
          <h1 class="post-title" itemprop="name headline">高性能MySQL读书笔记（四）</h1>
        

        <div class="post-meta">
          <span class="post-time">
            
              <span class="post-meta-item-icon">
                <i class="fa fa-calendar-o"></i>
              </span>
              
                <span class="post-meta-item-text">发表于</span>
              
              <time title="创建于" itemprop="dateCreated datePublished" datetime="2019-12-01T21:00:02+08:00">
                2019-12-01
              </time>
            

            

            
          </span>

          
            <span class="post-category">
            
              <span class="post-meta-divider">|</span>
            
              <span class="post-meta-item-icon">
                <i class="fa fa-folder-o"></i>
              </span>
              
                <span class="post-meta-item-text">分类于</span>
              
              
                <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
                  <a href="/categories/mysql/" itemprop="url" rel="index">
                    <span itemprop="name">mysql</span>
                  </a>
                </span>

                
                
              
            </span>
          

          
            
          

          
          

          

          

          

        </div>
      </header>
    

    
    
    
    <div class="post-body" itemprop="articleBody">

      
      

      
        <h1 id="高性能MySQL读书笔记（四）"><a href="#高性能MySQL读书笔记（四）" class="headerlink" title="高性能MySQL读书笔记（四）"></a>高性能MySQL读书笔记（四）</h1><blockquote>
<ul>
<li>数据类型</li>
<li>范式设计</li>
<li>大表的Alter操作</li>
<li>相关资料</li>
</ul>
</blockquote>
<h2 id="数据类型"><a href="#数据类型" class="headerlink" title="数据类型"></a>数据类型</h2><h3 id="整数与小数类型"><a href="#整数与小数类型" class="headerlink" title="整数与小数类型"></a>整数与小数类型</h3><blockquote>
<ul>
<li>整数类型 : 有如下几种：<br>TINYINT(1个字节),SMALLINT(2个字节),MEDIUMINT(3个字节),INT(4个字节),BIGINT(6个字节)</li>
</ul>
</blockquote>
<a id="more"></a> 

<blockquote>
<ul>
<li>小数（实数）类型有如下几种：<br>FLOAT4个字节)、DOUBLE(8个字节)、DECIMAL(占用字节更多)。<br>DECIMAL(n,m)表示仅允许保留m位小数；n表示小数点左右两边数字总和。也就是说只允许n-m个整数位。</li>
</ul>
</blockquote>
<h3 id="字符串类型"><a href="#字符串类型" class="headerlink" title="字符串类型"></a>字符串类型</h3><blockquote>
<ul>
<li>varchar是使用最频繁的字符串类型，它除了维护字符串还维护了一个或者两个字节用来记录长度。varchar(n)，n代表单个字符的个数,当我们设置varchar(5)或者varchar(100)来存储”mysql”，哪个会更好呢？当然是varchar(5)更好，因为varchar(100)即使之存储5个字符的字符串，也会分配100个字符的内存空间，特别是当使用临时表会更不好。</li>
</ul>
</blockquote>
<blockquote>
<ul>
<li>char用来存储较小的字符串 char(n)代表可以存储n个字符的字符串。</li>
</ul>
</blockquote>
<blockquote>
<ul>
<li>blob 与text<br>这两种类型是用来存储较大字符串的类型，与其他类型不同，它们的排序只按照最前边max_sort_length个字符进行排序，而不是按照全部字符排序。<br>因此如果只需要按照较小的字符排序，可以缩小max_sort_length的设定。或者使用 order by substring( column，length)进行排序。</li>
</ul>
</blockquote>
<blockquote>
<ul>
<li>enum 用时可以使用 enum(“string1”,“string2”,“string3”)代替varchar\char等字符串类型。<br>enum类型对字符，实际是使用整数进行存储的，并且当对enum列排序也是按照实际存储的整数值排序。适合存储如有序的状态，性别等等。<br>比如，我们新增一个enum类型的列，并按照主键id依次顺序插入：</li>
</ul>
</blockquote>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br></pre></td><td class="code"><pre><span class="line">ALTER TABLE USER ADD  enumType ENUM(&quot;apple&quot;,&quot;banana&quot;,&quot;orange&quot;);</span><br><span class="line">ALTER TABLE USER ADD  enumType ENUM(&quot;apple&quot;,&quot;banana&quot;,&quot;orange&quot;);</span><br><span class="line"></span><br><span class="line"></span><br><span class="line">UPDATE `user`.`user` SET `enumType` = &apos;apple&apos; WHERE id=1;</span><br><span class="line">UPDATE `user`.`user` SET `enumType` = &apos;banana&apos; WHERE id=2;</span><br><span class="line">UPDATE `user`.`user` SET `enumType` = &apos;orange&apos; WHERE id=3;</span><br><span class="line"></span><br><span class="line">SELECT id,(enumType+0) AS a FROM USER ;</span><br><span class="line"></span><br><span class="line">OUTPUT:</span><br><span class="line">id	enumType</span><br><span class="line">1	1</span><br><span class="line">2	2</span><br><span class="line">3	3</span><br></pre></td></tr></table></figure>

<p>可以看到，插入的 “apple”,“banana”,”orange”分别使用 1，2, 3进行存储，也就是按照enum设定的顺序。<br>当然对这个enum列的排序也是按照数字排序。</p>
<p>注意：因此在使用enum类型的时候，切记不要存储 如 enum(“10”,“29”,“42”,“99”)这有的字符串类型的数字，以避免产生不必要的混淆！</p>
<p>enum类型<br>优点：更紧凑的存储，更加节省空间；<br>不足：当enum类型的列 与 varchar类型的列进行关联时比两个varchar进行关联慢一些。</p>
<h3 id="日期与时间"><a href="#日期与时间" class="headerlink" title="日期与时间"></a>日期与时间</h3><blockquote>
<ul>
<li>DATETIME<br>datetime 用来表示日期，范围从1001年到9999年，精确到秒:YYYYMMDDHHMMSS，占用8个字节。这种类型与时区无关。</li>
</ul>
</blockquote>
<blockquote>
<ul>
<li>TIMESTAMP<br>从1970.1.1以来的描述，跟UNIX时间戳相同。<br>占用4个字节，也是精确到秒，但是范围要比DateTime小很多，1970~2038。<br>另外，这种类型默认not null，首次插入会自动用当前时间填充。</li>
</ul>
</blockquote>
<p>FROM_UNIXTIME()可以把时间戳转为日期。<br>UNIX_TIMESTAMP()可以把日期转为时间戳。</p>
<p>对于比秒更小的粒度：目前暂无支持，可以使用bigint存储更小的时间戳。</p>
<p><strong>数据类型优化技巧</strong></p>
<ul>
<li>数据类型更小通常更好，数据类型越简单越好</li>
<li>数值操作比字符操作快，小类型的处理速度比大类型快</li>
<li>尽量避免使用NULL，要是有必要用NULL，那也可考虑使用0来进行代替</li>
<li>IP用int存：inet_aton()、inet_ntoa()</li>
</ul>
<h2 id="范式设计"><a href="#范式设计" class="headerlink" title="范式设计"></a>范式设计</h2><p>第一范式：要求有主键，并且要求每一个字段原子性不可再分<br>第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖<br>第三范式：所有非主键字段和主键字段之间不能产生传递依赖</p>
<p>第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖<br>即满足第一范式前提，当存在多个主键的时候，才会发生不符合第二范式的情况。<br>比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。<br>通俗理解是任意一个字段都只依赖表中的同一个字段。（涉及到表的拆分）</p>
<p>第三范式：所有非主键字段和主键字段之间不能产生传递依赖<br>请看下边的例子：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">&quot;cid&quot;	&quot;role&quot;				&quot;phone&quot;			&quot;qqNumber&quot;			&quot;level&quot;</span><br><span class="line">&quot;1&quot;		&quot;programmer&quot;		&quot;15472152456&quot;	&quot;502327342&quot;			&quot;6&quot;</span><br><span class="line">&quot;2&quot;		&quot;manager&quot;			&quot;15372152456&quot;	&quot;534232&quot;			&quot;2&quot;</span><br><span class="line">&quot;3&quot;		&quot;manager&quot;			&quot;15372152436&quot;	&quot;5023272&quot;			&quot;3&quot;</span><br></pre></td></tr></table></figure>

<p>这个设计就违反了第三范式，因为level字段依赖于role，role依赖于cid：<br>cid 为1的员工 programmer 角色 级别为6;<br>cid 为2的员工 manager级别 级别为2；<br>cid 为3的员工 manager级别为3</p>
<p>– - 符合第二范式：</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">&quot;cid&quot;	&quot;phone&quot;			&quot;qqNumber&quot;</span><br><span class="line">&quot;1&quot;		&quot;15472152456&quot;	&quot;502327342&quot;</span><br><span class="line">&quot;2&quot;		&quot;15372152456&quot;	&quot;534232&quot;</span><br><span class="line">&quot;3&quot;		&quot;15372152436&quot;	&quot;5023272&quot;</span><br><span class="line"></span><br><span class="line">&quot;cid&quot;	&quot;role&quot;			&quot;level&quot;</span><br><span class="line">&quot;2&quot;		&quot;manager&quot;		&quot;3&quot;</span><br><span class="line">&quot;3&quot;		&quot;manager&quot;		&quot;2&quot;</span><br><span class="line">&quot;1&quot;		&quot;programmer&quot;	&quot;6&quot;</span><br></pre></td></tr></table></figure>

<p>换个例子，比如一个表中有 年龄\是否未成年 ；这两个字段明显存在依赖。</p>
<h3 id="范式设计的优缺点"><a href="#范式设计的优缺点" class="headerlink" title="范式设计的优缺点"></a>范式设计的优缺点</h3><blockquote>
<ul>
<li>优点：拆分的更细，冗余数据更少。占用空间更小，可以更好的放入内存，操作更快</li>
<li>会导致查询需要频繁使用多表关联,会导致执行效率降低、并且有些索引无法很好执行。</li>
</ul>
</blockquote>
<h3 id="反范式设计的优缺点"><a href="#反范式设计的优缺点" class="headerlink" title="反范式设计的优缺点"></a>反范式设计的优缺点</h3><blockquote>
<ul>
<li>优点：把所有业务相关的字段尽可能的放入一张表中，避免多表关联查询，单表可以更好的使用索引，有时候执行效率更高！</li>
<li>缺点：更多的冗余数据，导致更新或者删除缓慢。</li>
</ul>
</blockquote>
<p>一些策略：</p>
<ul>
<li><p>基于静态变更频率不高数据的冗余 ：静态数据指相关字段变更频率不高，如用户资料，可作冗余字段。<br>以消息表为例<br>Fuid，Tuid，Msg，Dateline<br>范式化设计后，在内容呈现上可能需要涉及多表关联，因为用户想知道发消息的人是谁？<br>那么，可增加冗余字段：Funame</p>
</li>
<li><p>基于谓词的冗余<br>  有些where连接条件很热门，一个谓词为多个SQL服务，那么，我们可以把这些热门的谓词收集，集中存在到一张冗余表，以用户登录为例子，假设有上亿条用户资料并做了分表处理，用户只关心uname，根据uname需要到各个分表去查询uid，此时，我们可按：uname，uid建立冗余表。</p>
</li>
<li><p>基于函数的冗余<br>  频繁的count，max，sum等需求，例如，论坛今日发帖总量，可建立冗余字段Spost</p>
</li>
<li><p>基于排序的冗余<br>  索引用来加速查询或者已经是件家喻户晓的事，但还有一个优势，即：降低排序成本。<br>  仍然以消息表为例，若我们按uname对消息做排序的代价估计会蛮高的，但如果在消息表里把uname冗余并且建立好索引，则可以借助索引来抵消排序.</p>
</li>
</ul>
<h2 id="大表的Alter操作"><a href="#大表的Alter操作" class="headerlink" title="大表的Alter操作"></a>大表的Alter操作</h2><p>总结一下Alter大表的几种方式：</p>
<p>先在一个不提供服务的数据库表中进行执行alter脚本，执行完毕后与提供服务的主库进行切换。当然要最大限度保证这两个库的数据是同步的。<br>影子拷贝：通过重命名替换表。<br>创建一个张影子表，新增需求字段，同步数据，同步一致后通过原子性的重命名操作替换两个表。</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br></pre></td><td class="code"><pre><span class="line">-- step1</span><br><span class="line">CREATE TABLE User_new LIKE USER;</span><br><span class="line">-- step2</span><br><span class="line">alter table User_new add column;//增加字段</span><br><span class="line">--step3</span><br><span class="line">同步数据，直到一致</span><br><span class="line">-- step4 原子替换</span><br><span class="line">RENAME TABLE USER TO User_old ,User_new TO USER</span><br><span class="line">在一个不提供服务的同步从库中执行alter，然后主从切换</span><br></pre></td></tr></table></figure>

<p>附一个db升级脚本</p>
<figure class="highlight plain"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line">ALTER TABLE `fight_over_log` ADD COLUMN `open_treasure_box_by_promt_button_times` int(11) default &apos;0&apos;;</span><br><span class="line">ALTER TABLE `fight_over_log` ADD COLUMN `ride_up_by_promt_button_times` int(11) default &apos;0&apos;;</span><br><span class="line"></span><br><span class="line">DROP PROCEDURE IF EXISTS `sp_heart_log`;</span><br><span class="line">DELIMITER ;;</span><br><span class="line">CREATE  PROCEDURE `sp_heart_log`(IN `_server_id` varchar(11),IN `_online_player_count` int,IN `_room_player_count` int,IN `_solo_room_user_single_leisure` int,IN `_solo_rooms_single_leisure` int,IN `_solo_room_user_single_rank` int,IN `_solo_rooms_single_rank` int,IN `_group_room_user_leisure` int,IN `_group_rooms_leisure` int,IN `_group_room_user_rank` int,IN `_group_rooms_rank` int,IN `_time_stamp` int)</span><br><span class="line">BEGIN</span><br><span class="line">		insert into heart_log (server_id,online_player_count,room_player_count,solo_room_user_single_leisure,solo_rooms_single_leisure,solo_room_user_single_rank,solo_rooms_single_rank,group_room_user_leisure,group_rooms_leisure,group_room_user_rank,group_rooms_rank,log_time) values(`_server_id`,`_online_player_count`,`_room_player_count`,`_solo_room_user_single_leisure`,`_solo_rooms_single_leisure`,`_solo_room_user_single_rank`,`_solo_rooms_single_rank`,`_group_room_user_leisure`,`_group_rooms_leisure`,`_group_room_user_rank`,`_group_rooms_rank`,FROM_UNIXTIME(`_time_stamp`));</span><br><span class="line">END</span><br><span class="line">;;</span><br><span class="line">DELIMITER ;</span><br></pre></td></tr></table></figure>

<p>注：db升级脚本要多加一行空白作为结束符，不然会执行报错= =</p>
<h2 id="相关资料"><a href="#相关资料" class="headerlink" title="相关资料"></a>相关资料</h2><p><a href="https://blog.csdn.net/lemon89/article/details/76651189" target="_blank" rel="noopener">参考资料</a></p>

      
    </div>
    
    
    

    

    
      <div>
        <div style="padding: 10px 0; margin: 20px auto; width: 90%; text-align: center;">
  <div></div>
  <button id="rewardButton" disable="enable" onclick="var qr = document.getElementById('QR'); if (qr.style.display === 'none') {qr.style.display='block';} else {qr.style.display='none'}">
    <span>打赏</span>
  </button>
  <div id="QR" style="display: none;">

    
      <div id="wechat" style="display: inline-block">
        <img id="wechat_qr" src="/images/wechatpay.jpg" alt="邱灵雨 微信支付">
        <p>微信支付</p>
      </div>
    

    
      <div id="alipay" style="display: inline-block">
        <img id="alipay_qr" src="/images/alipay.jpg" alt="邱灵雨 支付宝">
        <p>支付宝</p>
      </div>
    

    

  </div>
</div>

      </div>
    

    
      <div>
        
      <ul class="post-copyright">
         <li class="post-copyright-link">
          <strong>本文作者：</strong>
          <a href="/" title="欢迎访问 邱灵雨 的个人博客">邱灵雨</a>
        </li>

        <li class="post-copyright-link">
          <strong>本文链接：</strong>
          <a href="http://sabistian.cn/2019/12/01/高性能MySQL读书笔记(四)/" title="高性能MySQL读书笔记（四）">http://sabistian.cn/2019/12/01/高性能MySQL读书笔记(四)/</a>
        </li>

        <li class="post-copyright-license">
          <strong>版权声明： </strong>
          本文由 邱灵雨 原创，采用 <a href="https://creativecommons.org/licenses/by-nc-nd/4.0/deed.zh" rel="license" target="_blank">保留署名-非商业性使用-禁止演绎 4.0-国际许可协议</a> <br>转载请保留以上声明信息！
        </li>
      </ul>
<div style="text-align:center;color: #ccc;font-size:14px;">
------ 本文结束 ------</div>
      </div>
    

    <footer class="post-footer">
      
      
        <div class="post-tags">
          
            <a href="/tags/mysql/" rel="tag"><i class="fa fa-tag"></i> mysql</a>
          
        </div>
      

      
      
      

      
        <div class="post-nav">
          <div class="post-nav-next post-nav-item">
            
              <a href="/2019/11/23/读书笔记/" rel="next" title="游戏编程算法与技巧-读书笔记（一）">
                <i class="fa fa-chevron-left"></i> 游戏编程算法与技巧-读书笔记（一）
              </a>
            
          </div>

          <span class="post-nav-divider"></span>

          <div class="post-nav-prev post-nav-item">
            
              <a href="/2020/04/13/Leave/" rel="prev" title="坑边闲话">
                坑边闲话 <i class="fa fa-chevron-right"></i>
              </a>
            
          </div>
        </div>
      

      
      
    </footer>
  </div>
  
  
  
  </article>



    <div class="post-spread">
      
    </div>
  </div>


          </div>
          


          

  



        </div>
        
          
  
  <div class="sidebar-toggle">
    <div class="sidebar-toggle-line-wrap">
      <span class="sidebar-toggle-line sidebar-toggle-line-first"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-middle"></span>
      <span class="sidebar-toggle-line sidebar-toggle-line-last"></span>
    </div>
  </div>

  <aside id="sidebar" class="sidebar">
    
    <div class="sidebar-inner">

      

      
        <ul class="sidebar-nav motion-element">
          <li class="sidebar-nav-toc sidebar-nav-active" data-target="post-toc-wrap">
            文章目录
          </li>
          <li class="sidebar-nav-overview" data-target="site-overview-wrap">
            站点概览
          </li>
        </ul>
      

      <section class="site-overview-wrap sidebar-panel">
        <div class="site-overview">
          <div class="site-author motion-element" itemprop="author" itemscope itemtype="http://schema.org/Person">
            
              <img class="site-author-image" itemprop="image" src="/images/headicon.png" alt="邱灵雨">
            
              <p class="site-author-name" itemprop="name">邱灵雨</p>
              <p class="site-description motion-element" itemprop="description">灵雨既零</p>
          </div>

          <nav class="site-state motion-element">

            
              <div class="site-state-item site-state-posts">
              
                <a href="/archives/">
              
                  <span class="site-state-item-count">21</span>
                  <span class="site-state-item-name">日志</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-categories">
                <a href="/categories/index.html">
                  <span class="site-state-item-count">7</span>
                  <span class="site-state-item-name">分类</span>
                </a>
              </div>
            

            
              
              
              <div class="site-state-item site-state-tags">
                <a href="/tags/index.html">
                  <span class="site-state-item-count">20</span>
                  <span class="site-state-item-name">标签</span>
                </a>
              </div>
            

          </nav>

          

          
            <div class="links-of-author motion-element">
                
                  <span class="links-of-author-item">
                    <a href="https://github.com/sabistian" target="_blank" title="GitHub">
                      
                        <i class="fa fa-fw fa-github"></i>GitHub</a>
                  </span>
                
                  <span class="links-of-author-item">
                    <a href="mailto:sebistian@163.com" target="_blank" title="Mail">
                      
                        <i class="fa fa-fw fa-envelope"></i>Mail</a>
                  </span>
                
                  <span class="links-of-author-item">
                    <a href="https://weibo.com/5975979965/profile?rightmod=1&wvr=6&mod=personinfo" target="_blank" title="Weibo">
                      
                        <i class="fa fa-fw fa-weibo"></i>Weibo</a>
                  </span>
                
                  <span class="links-of-author-item">
                    <a href="https://www.zhihu.com/people/kuai-shen-l/activities" target="_blank" title="ZhiHu">
                      
                        <i class="fa fa-fw fa-globe"></i>ZhiHu</a>
                  </span>
                
            </div>
          

          
          

          
          

          

        </div>
      </section>

      
      <!--noindex-->
        <section class="post-toc-wrap motion-element sidebar-panel sidebar-panel-active">
          <div class="post-toc">

            
              
            

            
              <div class="post-toc-content"><ol class="nav"><li class="nav-item nav-level-1"><a class="nav-link" href="#高性能MySQL读书笔记（四）"><span class="nav-number">1.</span> <span class="nav-text">高性能MySQL读书笔记（四）</span></a><ol class="nav-child"><li class="nav-item nav-level-2"><a class="nav-link" href="#数据类型"><span class="nav-number">1.1.</span> <span class="nav-text">数据类型</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#整数与小数类型"><span class="nav-number">1.1.1.</span> <span class="nav-text">整数与小数类型</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#字符串类型"><span class="nav-number">1.1.2.</span> <span class="nav-text">字符串类型</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#日期与时间"><span class="nav-number">1.1.3.</span> <span class="nav-text">日期与时间</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#范式设计"><span class="nav-number">1.2.</span> <span class="nav-text">范式设计</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#范式设计的优缺点"><span class="nav-number">1.2.1.</span> <span class="nav-text">范式设计的优缺点</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#反范式设计的优缺点"><span class="nav-number">1.2.2.</span> <span class="nav-text">反范式设计的优缺点</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#大表的Alter操作"><span class="nav-number">1.3.</span> <span class="nav-text">大表的Alter操作</span></a></li><li class="nav-item nav-level-2"><a class="nav-link" href="#相关资料"><span class="nav-number">1.4.</span> <span class="nav-text">相关资料</span></a></li></ol></li></ol></div>
            

          </div>
        </section>
      <!--/noindex-->
      

      

    </div>
  </aside>


        
      </div>
    </main>

    <footer id="footer" class="footer">
      <div class="footer-inner">
        <script async src="https://busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script>

<div class="copyright">&copy; <span itemprop="copyrightYear">2020</span>
  <span class="with-love">
    <i class="fa fa-user"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">邱灵雨</span>

  
</div>

<div class="powered-by">
<i class="fa fa-user-md"></i><span id="busuanzi_container_site_uv">
  本站访客数:<span id="busuanzi_value_site_uv"></span>
</span>
</div>


  <div class="powered-by">由 <a class="theme-link" target="_blank" href="https://hexo.io">Hexo</a> 强力驱动</div>



  <span class="post-meta-divider">|</span>



  <div class="theme-info">主题 &mdash; <a class="theme-link" target="_blank" href="https://github.com/iissnan/hexo-theme-next">NexT.Gemini</a> v5.1.4</div>




        







        
      </div>
    </footer>

    
      <div class="back-to-top">
        <i class="fa fa-arrow-up"></i>
        
      </div>
    

    

  </div>

  

<script type="text/javascript">
  if (Object.prototype.toString.call(window.Promise) !== '[object Function]') {
    window.Promise = null;
  }
</script>









  












  
  
    <script type="text/javascript" src="/lib/jquery/index.js?v=2.1.3"></script>
  

  
  
    <script type="text/javascript" src="/lib/fastclick/lib/fastclick.min.js?v=1.0.6"></script>
  

  
  
    <script type="text/javascript" src="/lib/jquery_lazyload/jquery.lazyload.js?v=1.9.7"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/velocity/velocity.ui.min.js?v=1.2.1"></script>
  

  
  
    <script type="text/javascript" src="/lib/fancybox/source/jquery.fancybox.pack.js?v=2.1.5"></script>
  


  


  <script type="text/javascript" src="/js/src/utils.js?v=5.1.4"></script>

  <script type="text/javascript" src="/js/src/motion.js?v=5.1.4"></script>



  
  


  <script type="text/javascript" src="/js/src/affix.js?v=5.1.4"></script>

  <script type="text/javascript" src="/js/src/schemes/pisces.js?v=5.1.4"></script>



  
  <script type="text/javascript" src="/js/src/scrollspy.js?v=5.1.4"></script>
<script type="text/javascript" src="/js/src/post-details.js?v=5.1.4"></script>



  


  <script type="text/javascript" src="/js/src/bootstrap.js?v=5.1.4"></script>



  


  




	





  





  












  





  

  

  

  
  

  

  

  

</body>
</html>
